#! /bin/bash
HIVE_HOME=/usr/bin/hive
if [[ $1 == "" ]]
then
TD_DATE=`date -d '-1 day' "+%Y-%m-%d"`
else
TD_DATE=$1
fi

TD_YEAR=$(date -d "$TD_DATE" +%Y)
TD_MONTH=$(date -d "$TD_DATE" +%m)
TD_DAY=$(date -d "$TD_DATE" +%d)
month_for_quarter=`date -d "$TD_DATE" +%-m`
TD_QUARTER=$((($month_for_quarter-1)/3+1))

${HIVE_HOME} -S -e "
    --动态分区配置
    set hive.exec.dynamic.partition=true;
    set hive.exec.max.dynamic.partitions=2000;
    set hive.exec.dynamic.partition.mode=nonstrict;
    set hive.exec.max.dynamic.partitions.pernode=10000;
    set hive.exec.max.dynamic.partitions=100000;
    set hive.exec.max.created.files=150000;
    --hive压缩
    set hive.exec.compress.intermediate=true;
    set hive.exec.compress.output=true;
    --写入时压缩生效
    set hive.exec.orc.compression.strategy=COMPRESSION;

    ---------地区分组
    --小时
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        area,
        '-1' as seo_source,
        '-1' as origin_channel,
        hourinfo,
        quarterinfo,
        concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str,
        '-1' as from_url,
        '1' as grouptype,
        '1' as time_type,
        yearinfo, monthinfo, dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and monthinfo='$TD_MONTH' and dayinfo='$TD_DAY'
    group by area, yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo;

    --天
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        area,
        '-1' as seo_source,
        '-1' as origin_channel,
        '-1' as hourinfo,
        quarterinfo,
        concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str,
        '-1' as from_url,
        '1' as grouptype,
        '2' as time_type,
        yearinfo, monthinfo, dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and monthinfo='$TD_MONTH' and dayinfo='$TD_DAY'
    group by area, yearinfo, quarterinfo, monthinfo, dayinfo;

    --月
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        area,
        '-1' as seo_source,
        '-1' as origin_channel,
        '-1' as hourinfo,
        quarterinfo,
        concat(yearinfo,'-',monthinfo) as time_str,
        '-1' as from_url,
        '1' as grouptype,
        '3' as time_type,
        yearinfo, monthinfo,
        '-1' as dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and monthinfo='$TD_MONTH'
    group by area, yearinfo, quarterinfo, monthinfo;

    --季度
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        area,
        '-1' as seo_source,
        '-1' as origin_channel,
        '-1' as hourinfo,
        quarterinfo,
        concat(yearinfo,'-Q',quarterinfo) as time_str,
        '-1' as from_url,
        '1' as grouptype,
        '4' as time_type,
        yearinfo,
        '-1' as monthinfo,
        '-1' as dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and quarterinfo='$TD_QUARTER'
    group by area, yearinfo, quarterinfo;
    --年
    INSERT overwrite TABLE itcast_dws.visit_dws_demo PARTITION (yearinfo,monthinfo,dayinfo)
    select 
    COUNT(DISTINCT wce.sid) as sid_total,
    COUNT(DISTINCT wce.session_id) as sessionid_total,
    COUNT(DISTINCT wce.ip) as ip_total,
    wce.area as area,
    '-1' as seo_source,
    '-1' as origin_channel,
    '-1' as hourinfo,
    '-1' as quarterinfo,
    wce.yearinfo as time_str,
    '-1' as from_url,
    '1' as groupType,
    '5' as time_type,
    wce.yearinfo as yearinfo,
    '-1' as monthinfo,
    '-1' as dayinfo
    from itcast_dwd.visit_consult_dwd_demo wce
    where yearinfo='$TD_YEAR'
    group by wce.area,wce.yearinfo;


    ---------搜索来源分组
    --小时
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        '-1' as area,
        seo_source,
        '-1' as origin_channel,
        hourinfo,
        quarterinfo,
        concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str,
        '-1' as from_url,
        '2' as grouptype,
        '1' as time_type,
        yearinfo, monthinfo, dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and monthinfo='$TD_MONTH' and dayinfo='$TD_DAY'
    group by seo_source, yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo;

    --天
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        '-1' as area,
        seo_source,
        '-1' as origin_channel,
        '-1' as hourinfo,
        quarterinfo,
        concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str,
        '-1' as from_url,
        '2' as grouptype,
        '2' as time_type,
        yearinfo, monthinfo, dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and monthinfo='$TD_MONTH' and dayinfo='$TD_DAY'
    group by seo_source, yearinfo, quarterinfo, monthinfo, dayinfo;

    --月
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        '-1' as area,
        seo_source,
        '-1' as origin_channel,
        '-1' as hourinfo,
        quarterinfo,
        concat(yearinfo,'-',monthinfo) as time_str,
        '-1' as from_url,
        '2' as grouptype,
        '3' as time_type,
        yearinfo, monthinfo,
        '-1' as dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and monthinfo='$TD_MONTH'
    group by seo_source, yearinfo, quarterinfo, monthinfo;

    --季度
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        '-1' as area,
        seo_source,
        '-1' as origin_channel,
        '-1' as hourinfo,
        quarterinfo,
        concat(yearinfo,'-Q',quarterinfo) as time_str,
        '-1' as from_url,
        '2' as grouptype,
        '4' as time_type,
        yearinfo,
        '-1' as monthinfo,
        '-1' as dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and quarterinfo='$TD_QUARTER'
    group by seo_source, yearinfo, quarterinfo;
    --年
    INSERT overwrite TABLE itcast_dws.visit_dws_demo PARTITION (yearinfo,monthinfo,dayinfo)
    select 
    COUNT(DISTINCT wce.sid) as sid_total,
    COUNT(DISTINCT wce.session_id) as sessionid_total,
    COUNT(DISTINCT wce.ip) as ip_total,
    '-1' as  area,
    seo_source,
    '-1' as origin_channel,
    '-1' as hourinfo,
    '-1' as quarterinfo,
    wce.yearinfo as time_str,
    '-1' as from_url,
    '2' as groupType,
    '5' as time_type,
    wce.yearinfo as yearinfo,
    '-1' as monthinfo,
    '-1' as dayinfo
    from itcast_dwd.visit_consult_dwd_demo wce
    where yearinfo='$TD_YEAR'
    group by wce.seo_source,wce.yearinfo;

    ---------来源渠道分组
    --小时
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        '-1' as area,
        '-1' as seo_source,
        origin_channel,
        hourinfo,
        quarterinfo,
        concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str,
        '-1' as from_url,
        '3' as grouptype,
        '1' as time_type,
        yearinfo, monthinfo, dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and monthinfo='$TD_MONTH' and dayinfo='$TD_DAY'
    group by origin_channel, yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo;

    --天
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        '-1' as area,
        '-1' as seo_source,
        origin_channel,
        '-1' as hourinfo,
        quarterinfo,
        concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str,
        '-1' as from_url,
        '3' as grouptype,
        '2' as time_type,
        yearinfo, monthinfo, dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and monthinfo='$TD_MONTH' and dayinfo='$TD_DAY'
    group by origin_channel, yearinfo, quarterinfo, monthinfo, dayinfo;

    --月
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        '-1' as area,
        '-1' as seo_source,
        origin_channel,
        '-1' as hourinfo,
        quarterinfo,
        concat(yearinfo,'-',monthinfo) as time_str,
        '-1' as from_url,
        '3' as grouptype,
        '3' as time_type,
        yearinfo, monthinfo,
        '-1' as dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and monthinfo='$TD_MONTH'
    group by origin_channel, yearinfo, quarterinfo, monthinfo;

    --季度
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        '-1' as area,
        '-1' as seo_source,
        origin_channel,
        '-1' as hourinfo,
        quarterinfo,
        concat(yearinfo,'-Q',quarterinfo) as time_str,
        '-1' as from_url,
        '3' as grouptype,
        '4' as time_type,
        yearinfo,
        '-1' as monthinfo,
        '-1' as dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and quarterinfo='$TD_QUARTER'
    group by origin_channel, yearinfo, quarterinfo;
    --年
    INSERT overwrite TABLE itcast_dws.visit_dws_demo PARTITION (yearinfo,monthinfo,dayinfo)
    select 
    COUNT(DISTINCT wce.sid) as sid_total,
    COUNT(DISTINCT wce.session_id) as sessionid_total,
    COUNT(DISTINCT wce.ip) as ip_total,
    '-1' as  area,
    '-1' as seo_source,
    origin_channel,
    '-1' as hourinfo,
    '-1' as quarterinfo,
    wce.yearinfo as time_str,
    '-1' as from_url,
    '3' as groupType,
    '5' as time_type,
    wce.yearinfo as yearinfo,
    '-1' as monthinfo,
    '-1' as dayinfo
    from itcast_dwd.visit_consult_dwd_demo wce
    where yearinfo='$TD_YEAR'
    group by wce.origin_channel,wce.yearinfo;


    ---------会话来源页面分组
    --小时
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        '-1' as area,
        '-1' as seo_source,
        '-1' as origin_channel,
        hourinfo,
        quarterinfo,
        concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str,
        from_url,
        '4' as grouptype,
        '1' as time_type,
        yearinfo, monthinfo, dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and monthinfo='$TD_MONTH' and dayinfo='$TD_DAY'
    group by from_url, yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo;

    --天
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        '-1' as area,
        '-1' as seo_source,
        '-1' as origin_channel,
        '-1' as hourinfo,
        quarterinfo,
        concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str,
        from_url,
        '4' as grouptype,
        '2' as time_type,
        yearinfo, monthinfo, dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and monthinfo='$TD_MONTH' and dayinfo='$TD_DAY'
    group by from_url, yearinfo, quarterinfo, monthinfo, dayinfo;

    --月
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        '-1' as area,
        '-1' as seo_source,
        '-1' as origin_channel,
        '-1' as hourinfo,
        quarterinfo,
        concat(yearinfo,'-',monthinfo) as time_str,
        from_url,
        '4' as grouptype,
        '3' as time_type,
        yearinfo, monthinfo,
        '-1' as dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and monthinfo='$TD_MONTH'
    group by from_url, yearinfo, quarterinfo, monthinfo;

    --季度
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        '-1' as area,
        '-1' as seo_source,
        '-1' as origin_channel,
        '-1' as hourinfo,
        quarterinfo,
        concat(yearinfo,'-Q',quarterinfo) as time_str,
        from_url,
        '4' as grouptype,
        '4' as time_type,
        yearinfo,
        '-1' as monthinfo,
        '-1' as dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and quarterinfo='$TD_QUARTER'
    group by from_url, yearinfo, quarterinfo;
    --年
    INSERT overwrite TABLE itcast_dws.visit_dws_demo PARTITION (yearinfo,monthinfo,dayinfo)
    select 
    COUNT(DISTINCT wce.sid) as sid_total,
    COUNT(DISTINCT wce.session_id) as sessionid_total,
    COUNT(DISTINCT wce.ip) as ip_total,
    '-1' as  area,
    '-1' as seo_source,
    '-1' as origin_channel,
    '-1' as hourinfo,
    '-1' as quarterinfo,
    wce.yearinfo as time_str,
    from_url,
    '4' as groupType,
    '5' as time_type,
    wce.yearinfo as yearinfo,
    '-1' as monthinfo,
    '-1' as dayinfo
    from itcast_dwd.visit_consult_dwd_demo wce
    where yearinfo='$TD_YEAR'
    group by wce.from_url,wce.yearinfo;

    ---------总访问量分组
    --小时
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        '-1' as area,
        '-1' as seo_source,
        '-1' as origin_channel,
        hourinfo,
        quarterinfo,
        concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str,
        '-1' as from_url,
        '5' as grouptype,
        '1' as time_type,
        yearinfo, monthinfo, dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and monthinfo='$TD_MONTH' and dayinfo='$TD_DAY'
    group by yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo;

    --天
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        '-1' as area,
        '-1' as seo_source,
        '-1' as origin_channel,
        '-1' as hourinfo,
        quarterinfo,
        concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str,
        '-1' as from_url,
        '5' as grouptype,
        '2' as time_type,
        yearinfo, monthinfo, dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and monthinfo='$TD_MONTH' and dayinfo='$TD_DAY'
    group by yearinfo, quarterinfo, monthinfo, dayinfo;

    --月
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        '-1' as area,
        '-1' as seo_source,
        '-1' as origin_channel,
        '-1' as hourinfo,
        quarterinfo,
        concat(yearinfo,'-',monthinfo) as time_str,
        '-1' as from_url,
        '5' as grouptype,
        '3' as time_type,
        yearinfo, monthinfo,
        '-1' as dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and monthinfo='$TD_MONTH'
    group by yearinfo, quarterinfo, monthinfo;

    --季度
    insert into itcast_dws.visit_dws_demo partition (yearinfo, monthinfo, dayinfo)
    select 
        count(distinct sid) as sid_total,
        count(distinct session_id) as session_total,
        count(distinct ip) as ip_total,
        '-1' as area,
        '-1' as seo_source,
        '-1' as origin_channel,
        '-1' as hourinfo,
        quarterinfo,
        concat(yearinfo,'-Q',quarterinfo) as time_str,
        '-1' as from_url,
        '5' as grouptype,
        '4' as time_type,
        yearinfo,
        '-1' as monthinfo,
        '-1' as dayinfo
    from itcast_dwd.visit_consult_dwd_demo 
    where yearinfo='$TD_YEAR' and quarterinfo='$TD_QUARTER'
    group by yearinfo, quarterinfo;
    --年
    INSERT overwrite TABLE itcast_dws.visit_dws_demo PARTITION (yearinfo,monthinfo,dayinfo)
    select 
    COUNT(DISTINCT wce.sid) as sid_total,
    COUNT(DISTINCT wce.session_id) as sessionid_total,
    COUNT(DISTINCT wce.ip) as ip_total,
    '-1' as  area,
    '-1' as seo_source,
    '-1' as origin_channel,
    '-1' as hourinfo,
    '-1' as quarterinfo,
    wce.yearinfo as time_str,
    '-1' as from_url,
    '5' as groupType,
    '5' as time_type,
    wce.yearinfo as yearinfo,
    '-1' as monthinfo,
    '-1' as dayinfo
    from itcast_dwd.visit_consult_dwd_demo wce
    where yearinfo='$TD_YEAR'
    group by wce.yearinfo;
    "